﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Collections;
using System.Collections.ObjectModel;

using System.Data;
using System.Data.SqlClient;
using TimeUpdate.Help;

namespace TimeUpdate.Access
{
    class HelpCom
    {
        /// <summary>
        /// 更新单张表
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        private static SqlDataLayer sqlHelper = new SqlDataLayer();
        public static bool UpdateSingletable(DataTable dt, string tableName)
        {
            try
            {
                //先遍历整张表，如果是unchanged的不要理会

                //如果是add的话执行insert，如果是update的话执行
                //DataTable dt=new DataTable();
                //return dt;

                //更新数据库的语句
                string updateSQL = GetUpdateSQL(dt, tableName);
                //插入数据库的
                string insertSQL = GetInsertSQL(dt, tableName);
                //删除数据库的语句
                string delSQL = GetDelSQL(dt, tableName);
                string sql = "";
                if (updateSQL != "ERROR")
                {
                    sql += updateSQL;
                }
                if (insertSQL != "ERROR")
                {
                    sql += insertSQL;
                }
                if (delSQL != "ERROR")
                {
                    sql += delSQL;
                }

                //string sql = updateSQL + " " + insertSQL + " " + delSQL;

                sqlHelper.ExecuteSql(sql);

                return true;
            }
            catch (Exception ex)
            {
                // Global.GlobalVariable.global.log.writeError("UpdateSingletable" + ex.Message);
                return false;
            }
        }
        /// <summary>
        /// 获取update语句
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private static string GetUpdateSQL(DataTable dt, string tableName)
        {
            try
            {
                //获取主键列
                string primaryKey = dt.PrimaryKey[0].ColumnName;
                string updateSQL = "";
                foreach (DataRow dr in dt.Rows)
                {
                    string update = "";
                    if (dr.RowState == DataRowState.Modified)
                    {
                        update = "Update " + tableName + " set ";
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            if (dt.Columns[i].AutoIncrement == false)
                            {
                                string temp = "";
                                if (dr[i].ToString().ToLower() == "true")
                                {
                                    temp = "1";

                                }
                                else if (dr[i].ToString().ToLower() == "false")
                                {
                                    temp = "0";
                                }
                                else
                                {
                                    temp = dr[i].ToString();
                                }

                                update = update + dt.Columns[i].ColumnName + "='" + temp + "',";
                            }
                        }
                        if (update.EndsWith(","))
                        {
                            update = update.Substring(0, update.Length - 1);
                        }
                        update = update + " where " + primaryKey + "='" + dr[primaryKey] + "';";


                    }
                    updateSQL += update;
                }
                return updateSQL;
            }
            catch (Exception ex)
            {

                return "ERROR";
            }
        }

        /// <summary>
        /// 获取insert语句
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private static string GetInsertSQL(DataTable dt, string tableName)
        {
            try
            {
                string insertSQL = "";
                foreach (DataRow dr in dt.Rows)
                {
                    string insert = "";
                    if (dr.RowState == DataRowState.Added)
                    {
                        string colName = "";
                        string values = "";
                        insert = "insert " + tableName;
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            if (dt.Columns[i].AutoIncrement == false)
                            {
                                string temp = "";
                                if (dr[i].ToString().ToLower() == "true")
                                {
                                    temp = "1";

                                }
                                else if (dr[i].ToString().ToLower() == "false")
                                {
                                    temp = "0";
                                }
                                else
                                {
                                    temp = dr[i].ToString();
                                }
                                colName = colName + dt.Columns[i].ColumnName + ",";
                                values = values + "'" + temp + "'" + ",";
                            }
                        }
                        if (colName.EndsWith(","))
                        {
                            colName = colName.Substring(0, colName.Length - 1);
                        }

                        if (values.EndsWith(","))
                        {
                            values = values.Substring(0, values.Length - 1);
                        }
                        insert = insert + "(" + colName + ")" + " values (" + values + ");";


                    }
                    insertSQL += insert;
                }
                return insertSQL;
            }
            catch (Exception ex)
            {
                return "ERROR";
            }
        }
        /// <summary>
        /// 获取delete语句
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private static string GetDelSQL(DataTable dt, string tableName)
        {
            try
            {
                //获取主键列
                string primaryKey = dt.PrimaryKey[0].ColumnName;
                string DelSQL = "";
                foreach (DataRow dr in dt.Rows)
                {
                    string del = "";
                    if (dr.RowState == DataRowState.Deleted)
                    {
                        del = "delete " + tableName;

                        del = del + " where " + primaryKey + "='" + dr[primaryKey, DataRowVersion.Original].ToString() + "';";
                        //string id = ;

                    }
                    DelSQL += del;
                }
                return DelSQL;
            }
            catch (Exception ex)
            {
                return "ERROR";
            }
        }
    }
}
